
USE [SIHMENDOCRI]
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[ser_fSplit]') and xtype = 'TF')
	DROP FUNCTION [dbo].[ser_fSplit]
GO


CREATE FUNCTION ser_fSplit(@CADENA VARCHAR(8000), @DELIMITADOR CHAR(1))         
RETURNS @TEMPTABLA TABLE (ITEMS VARCHAR(8000))         
AS         
BEGIN         
    DECLARE @IDX INT         
    DECLARE @SLICE VARCHAR(8000)         
        
    SELECT @IDX = 1         
        IF LEN(@CADENA)<1 OR @CADENA IS NULL  RETURN         
        
    WHILE @IDX!= 0         
    BEGIN         
        SET @IDX = CHARINDEX(@DELIMITADOR,@CADENA)         
        IF @IDX!=0         
            SET @SLICE = LEFT(@CADENA,@IDX - 1)         
        ELSE         
            SET @SLICE = @CADENA         
            
        IF(LEN(@SLICE)>0)    
            INSERT INTO @TEMPTABLA(ITEMS) VALUES(@SLICE)         
    
        SET @CADENA = RIGHT(@CADENA,LEN(@CADENA) - @IDX)         
        IF LEN(@CADENA) = 0 BREAK         
    END     
RETURN         
END 
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_fHistoriasCompletas]') and xtype = 'TF')
	DROP FUNCTION [dbo].[fn_fHistoriasCompletas]
GO


CREATE FUNCTION fn_fHistoriasCompletas(@NUMCONSIDERACIONES INT,@NUMHISTORIA VARCHAR(20), @ESENTRENAMIENTO CHAR(2))         
RETURNS @TEMPTABLA TABLE (ITEMS INTEGER)         
AS         
BEGIN 

	IF(@ESENTRENAMIENTO = 'SI')
	BEGIN
		INSERT INTO @TEMPTABLA(ITEMS)
		SELECT		HC.IDHISTORIACLINICA
		FROM		[dbo].[HM_HISTORIACLINICA] HC
		RIGHT JOIN  [dbo].[HM_EVALUACION] EV ON HC.IDHISTORIACLINICA = EV.IDHISTORIACLINICA
		LEFT JOIN  [dbo].[HM_DETALLEENFERMEDAD] DENF ON EV.IDDETALLEENFERMEDAD = DENF.IDDETALLEENFERMEDAD
		WHERE		DENF.USOSISTEMAINTELIGENTE = 'SI'
					AND (RTRIM(LTRIM( VALOR1)) <>'' OR VALOR2 in ('0','1'))
					AND RTRIM(LTRIM( VALOR1)) <> '- ////' 
		GROUP BY	HC.IDHISTORIACLINICA,IDPACIENTE, NUMEROHISTORIA,SINDROMEMETABOLICO
		HAVING		COUNT(*) >=@NUMCONSIDERACIONES  -- SOLO HISTORIAS COMPLETAS HASTA EN 10 COINSIDENCIAS

	END
	ELSE
	BEGIN
		INSERT INTO @TEMPTABLA(ITEMS)
		SELECT		HC.IDHISTORIACLINICA
		FROM		[dbo].[HM_HISTORIACLINICA] HC
		RIGHT JOIN  [dbo].[HM_EVALUACIONPARADIAGNOSTICO] EV ON HC.IDHISTORIACLINICA = EV.IDHISTORIACLINICA
		LEFT JOIN  [dbo].[HM_DETALLEENFERMEDAD] DENF ON EV.IDDETALLEENFERMEDAD = DENF.IDDETALLEENFERMEDAD
		WHERE		DENF.USOSISTEMAINTELIGENTE = 'SI'
					AND (RTRIM(LTRIM( VALOR1)) <>'' OR VALOR2 in ('0','1'))
					AND RTRIM(LTRIM( VALOR1)) <> '- ////' 
		GROUP BY	HC.IDHISTORIACLINICA,IDPACIENTE, NUMEROHISTORIA,SINDROMEMETABOLICO
		HAVING		COUNT(*) >= @NUMCONSIDERACIONES  -- SOLO HISTORIAS COMPLETAS HASTA EN 10 COINSIDENCIAS

	END

	RETURN
END


